﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;
using Model;

namespace LogisticsDAL
{
    public class DriverDAL
    {
        /// <summary>
        /// 驾驶员页面显示与查询
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="pageSize"></param>
        /// <param name="count"></param>
        /// <param name="drs"></param>
        /// <returns></returns>
        public List<Drivers> Binddriver(int pageIndex,int pageSize,out int count,Drivers drs)
        {
            string TableName = "View_BindDriver";
            string pramary = "DriverID";
            string Filed = "1=1";
            if (!string.IsNullOrWhiteSpace(drs.Name))
            {
                Filed = Filed + " and Name like '%" + drs.Name + "%'";
            }
            if (drs.Sex != "-1")
            {
                Filed = Filed + " and Sex='" + drs.Sex + "'";
            }
            if (!string.IsNullOrWhiteSpace(drs.IDCard))
            {
                Filed = Filed + " and IDCard='" + drs.IDCard + "'";
            }
            if (!string.IsNullOrWhiteSpace(drs.Phone))
            {
                Filed = Filed + " and Phone='" + drs.Phone + "'";
            }
            if (drs.Birth != DateTime.MinValue && drs.BirthEnd != DateTime.MinValue)
            {
                Filed = @"" + Filed + " and (Birth>='" + drs.Birth + "' and Birth<='" + drs.BirthEnd + "'";
            }
            if (drs.TeamName != "0")
            {
                Filed = Filed + " and TeamName='" + drs.TeamName + "'";
            }
            if (drs.FK_TruckID != "0")
            {
                if (drs.FK_TruckID == "1")
                {
                    Filed = Filed + " and FK_TruckID is null";
                }
                else if (drs.FK_TruckID == "2")
                {
                    Filed = Filed + " and FK_TruckID is not null";
                }
            }
            if (drs.CheckInTime != DateTime.MinValue && drs.CheckdateEnd != DateTime.MinValue)
            {
                Filed = @"" + Filed + " and (CheckInTime>='" + drs.CheckInTime + "' and CheckInTime<='" + drs.CheckdateEnd + "'";
            }
            DataTable tb = PROC_Helper.Paging(TableName, pramary,pageIndex,pageSize,Filed,out count);
            List<Drivers> der = new List<Drivers>();
            if (tb.Rows.Count > 0)
            {
                foreach (DataRow item in tb.Rows)
                {
                    der.Add(new Drivers() {
                        DriverID =Convert.ToInt32(item["DriverID"]),
                        Name = item["Name"].ToString(),
                        Sex = item["Sex"].ToString(),
                        Phone = item["Phone"].ToString(),
                        Birth = Convert.ToDateTime(item["Birth"]),
                        BirthEnd = Convert.ToDateTime(item["Birth"]),
                        TeamName = item["TeamName"].ToString(),
                        IDCard = item["IDCard"].ToString(),
                        FK_TruckID = item["FK_TruckID"].ToString(),
                        CheckInTime=Convert.ToDateTime(item["CheckInTime"]),
                        CheckdateEnd=Convert.ToDateTime(item["CheckInTime"]),
                        Number=item["Number"].ToString()
                    });

                }
                

            }
            return der;
        }

        public DataTable ddlbindCarTeam()
        {
            string sql = "SELECT  [TeamName],TeamID from TruckTeam ";
            SqlParameter[] pram =
           {

            };
            DataTable dt = DBHelper.GetTable(sql, pram);
            return dt;
        }

        /// <summary>
        /// 条件查询驾驶员
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="Sex"></param>
        /// <param name="phone"></param>
        /// <param name="bRith"></param>
        /// <param name="TeamNaem"></param>
        /// <param name="IsDelete"></param>
        /// <param name="IDcard"></param>
        /// <param name="checkTime"></param>
        /// <returns></returns>
        public DataTable SelectDriver(string Name,string Sex,string phone,string bRith,string TeamNaem,string fktruck, string IDcard,string checkTime)
        {
            
            StringBuilder sql = new StringBuilder("select d1.DriverID,Name,Sex,Phone,Birth,TeamName,IDCard,d1.CheckInTime,Number,c1.FK_TruckID from Driver d1 left join Contact c1 on d1.DriverID=c1.FK_DriverID left join Truck t1 on t1.TruckID=c1.FK_TruckID inner join TruckTeam t2 on d1.FK_TeamID=t2.TeamID ");
            List<string> wherelist = new List<string>();
            List<SqlParameter> pramlist = new List<SqlParameter>();
            if (!string.IsNullOrWhiteSpace(Name))
            {
                wherelist.Add("Name like '%'+@Name+'%'");
                pramlist.Add(new SqlParameter("@Name", Name));
            }
            if (Sex != "-1")
            {
                wherelist.Add("Sex=@Sex");
                pramlist.Add(new SqlParameter("@Sex", Sex));
            }
            if (!string.IsNullOrWhiteSpace(phone))
            {
                wherelist.Add("Phone like '%'+@Phone+'%'");
                pramlist.Add(new SqlParameter("@Phone", phone));
            }
            if (TeamNaem != "0")
            {
                wherelist.Add("TeamName=@TeamName");
                pramlist.Add(new SqlParameter("@TeamName", TeamNaem));
            }
            if (!string.IsNullOrWhiteSpace(IDcard))
            {
                wherelist.Add("IDCard=@IDCard");
                pramlist.Add(new SqlParameter("@IDCard",IDcard));
            }
            if (fktruck != "0")
            {
                if (fktruck == "2")
                {
                   wherelist.Add("FK_TruckID != ''");
                }
                else if(fktruck=="1")
                {
                    wherelist.Add("FK_TruckID is null");
                }
                   
            }
            DateTime checkdate;
            if (!string.IsNullOrWhiteSpace(checkTime) && DateTime.TryParse(checkTime,out checkdate))
            {
                wherelist.Add("CheckInTime=@CheckInTime");
                pramlist.Add(new SqlParameter("@CheckInTime", checkTime));
            }
            if (wherelist.Count > 0)
            {
                string str = String.Join(" and ", wherelist.ToArray());
                sql.Append($" where {str}");
            }
           
            DataTable dt = DBHelper.GetTable(sql.ToString(), pramlist.ToArray());
            //List<Drivers> drv =new List<Drivers>();
            //if (dt.Rows.Count > 0) {
            //    foreach(DataRow item in dt.Rows)
            //    {
            //        drv.Add(new Drivers()
            //        {
            //            Name = item["Name"].ToString(),
            //            Phone=item["phone"].ToString(),
            //            Sex=item["Sex"].ToString(),
            //            TeamName=item["TeamName"].ToString(),
            //            Birth=Convert.ToDateTime(item["Birth"])
            //        });
            //    }
            //}
            return dt;
        }


        /// <summary>
        /// 添加驾驶员
        /// </summary>
        /// <param name="Name"></param>
        /// <param name="sex"></param>
        /// <param name="brith"></param>
        /// <param name="phone"></param>
        /// <param name="idcard"></param>
        /// <param name="truckid"></param>
        /// <param name="state"></param>
        /// <param name="remark"></param>
        /// <param name="checktime"></param>
        /// <returns></returns>
        public int AddDrivers(string Name,string sex,string brith,string phone,string idcard,string truckid,string state,string remark,string checktime)
        {
            string sql = "insert into Driver values(@Name,@Sex,@Brith,@Phone,@IDcard,@TruckID,@State,@Remark,@CheckTime,'0',GetDate())";
            SqlParameter[] prams =
            {
                new SqlParameter("@Name",Name),
                new SqlParameter("@Sex",sex),
                new SqlParameter("@Brith",brith),
                new SqlParameter("@Phone",phone),
                new SqlParameter("@IDcard",idcard),
                new SqlParameter("@TruckID",truckid),
                new SqlParameter("@State",truckid),
                new SqlParameter("@Remark",state),
                new SqlParameter("@CheckTime",checktime)
            };
            return DBHelper.Numbers(sql, prams);
        }

        /// <summary>
        /// 删除驾驶原信息
        /// </summary>
        /// <param name="did"></param>
        /// <returns></returns>
        public int delDriver(int did)
        {
            string sql = "delete  Driver where DriverID=@Driverid";
            SqlParameter[] pram =
            {
                new SqlParameter("@Driverid",did)
            };
            int result = DBHelper.Numbers(sql, pram);
            return result;
        }

        /// <summary>
        /// 修改驾驶员信息
        /// </summary>
        /// <returns></returns>
        public int updateDriver(Drivers drv)
        {
            StringBuilder sql =new StringBuilder("update Driver set [Name]=@name,[Sex]=@sex ,[Birth]=@birth ,[Phone]=@phone,[IDCard]=@idcard,[FK_TeamID]=@teamid,[State]=@state,[Remark]=@reamrk,[CheckInTime]=@checktime where DriverID=@Driverid");
            SqlParameter[] pram =
            {
                new SqlParameter("@name",drv.Name),
                new SqlParameter("@sex",drv.Sex),
                new SqlParameter("@birth",drv.Birth),
                new SqlParameter("@phone",drv.Phone),
                new SqlParameter("@idcard",drv.IDCard),
                new SqlParameter("@teamid",drv.FK_TeamID),
                new SqlParameter("@state",drv.State),
                new SqlParameter("@reamrk",drv.Remark),
                new SqlParameter("@checktime",drv.CheckInTime),
                new SqlParameter("@Driverid",drv.DriverID)
            };
            int result = DBHelper.Numbers(sql.ToString(), pram);
      
            return result;
        }

        public Drivers UpBindDriver(int drids)
        {
            string sql = "SELECT  [DriverID],[Name],[Sex] ,[Birth] ,[Phone],[IDCard],[FK_TeamID],[State],[Remark],[CheckInTime] FROM Driver  where DriverID=@DriverID";
            SqlParameter[] pram =
            {
                new SqlParameter("@DriverID",drids)
            };
            SqlDataReader read = DBHelper.getExreaders(sql, pram);
            Drivers dr = new Drivers();
            if (read.HasRows)
            {
                while (read.Read()) { 
                dr.DriverID =Convert.ToInt32(read["DriverID"]);
                dr.Name = read["Name"].ToString();
                dr.Sex = read["Sex"].ToString();
                dr.Birth = Convert.ToDateTime(read["Birth"]);
                dr.Phone = read["Phone"].ToString();
                dr.IDCard = read["IDCard"].ToString();
                dr.FK_TeamID =Convert.ToInt32(read["FK_TeamID"]);
                dr.State = read["State"].ToString();
                dr.Remark = read["Remark"].ToString();
                dr.CheckInTime = Convert.ToDateTime(read["CheckInTime"]);
            }
            }
            return dr;
           
        }


        /// <summary>
        /// 获取ID判断是否让删除
        /// </summary>
        public int GetDriversDId(int did)
        {
            string sql = "SELECT count(1) FROM [Driver] where DriverID = @DriverID";
            SqlParameter[] pram =
            {
                new SqlParameter("@DriverID",did)
            };
            int a = Convert.ToInt32(DBHelper.GetObject(sql, pram));
            return a;
        }
        public int GetContactDId(int did)
        {
            string sql = "SELECT count(1) FROM [Contact] where FK_DriverID = @fkDriverID";
            SqlParameter[] pram =
            {
                new SqlParameter("@fkDriverID",did)
            };
            int a = Convert.ToInt32(DBHelper.GetObject(sql, pram));
            return a;
        }


        /// <summary>
        /// 驾驶员解除绑定
        /// </summary>
        /// <param name="tuckid"></param>
        /// <returns></returns>
        public int unbound(int tuckid)
        {
            string sql = "delete Contact where FK_TruckID=@fktruckid";
            SqlParameter[] pram =
            {
                new SqlParameter("@fktruckid",tuckid)
            };
            int rusult = DBHelper.Numbers(sql, pram);
            return rusult;
        }


        /// <summary>
        /// 驾驶员车辆绑定
        /// </summary>
        /// <returns></returns>
        public DataTable bindtruck(int drivrid,int pageIndex,int pageSize,out int count)
        {
            string TableName = "Truck";
            string Pramary = "TruckID";
            string Filer = @"(TeamID =(SELECT   FK_TeamID FROM  dbo.Driver WHERE   (DriverID = "+drivrid+@")))
                    AND(TruckID NOT IN(SELECT   FK_TruckID FROM   dbo.Contact))"; 
            return PROC_Helper.Paging(TableName,Pramary,pageIndex,pageSize,Filer,out count);
        }

        public int insetBindTruck(int fktruckid,int driverid)
        {
            string sql = "insert into Contact values(@fktruckID,@fkdriverID)";
            SqlParameter[] pram =
            {
                new SqlParameter("@fktruckID",fktruckid),
                new SqlParameter("@fkdriverID",driverid)
            };
            return DBHelper.Numbers(sql, pram);
        }

        public int sqlealerTruckState(int drid)
        {
            string sql = "SELECT dbo.Truck.State,dbo.Driver.State AS Sta1 FROM dbo.Driver INNER JOIN dbo.Contact ON Contact.FK_DriverID = Driver.DriverID INNER JOIN dbo.Truck ON Truck.TruckID = Contact.FK_TruckID WHERE FK_TruckID=@driverid";
            int str = 0;
            SqlParameter[] pram =
            {
                new SqlParameter("@driverid",drid)
            };
            SqlDataReader read = DBHelper.getExreaders(sql, pram);
            if (read.HasRows)
            {
                while (read.Read())
                {
                     str = Convert.ToInt32(read["State"]);
                    
                }
            }
            return str;
        }

        public int sqlealerDriverState(int drid)
        {
            string sql = "SELECT dbo.Driver.State  FROM dbo.Driver INNER JOIN dbo.Contact ON Contact.FK_DriverID = Driver.DriverID INNER JOIN dbo.Truck ON Truck.TruckID = Contact.FK_TruckID WHERE FK_TruckID=@driverid";
            int sta = 0;
            SqlParameter[] pram =
            {
                new SqlParameter("@driverid",drid)
            };
            SqlDataReader read = DBHelper.getExreaders(sql, pram);
            if (read.HasRows)
            {
                while (read.Read())
                {
                    sta = Convert.ToInt32(read["State"]);

                }
            }
            return sta;
        }
    }
}
